package org.jeecg.modules.mes.storage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.jeecg.modules.mes.storage.entity.MesStorageWholesale;
import org.jeecg.modules.mes.storage.vo.ReportVo;
import org.jeecg.modules.mes.storage.vo.YclRkdReport;

import java.util.List;

/**
 * @Description: 仓库管理—批号交易
 * @Author: jeecg-boot
 * @Date: 2020-09-14
 * @Version: V1.0
 */
public interface MesStorageWholesaleMapper extends BaseMapper<MesStorageWholesale> {

    //原材料入库单
    public List<ReportVo> selectYclRkd(Page<ReportVo> page, @Param("begindate") String begindate, @Param("ids") List<String> ids, @Param("attr1") String attr1);

    //成品入库单
    public List<ReportVo> selectCpRkd(Page<ReportVo> page, @Param("begindate") String begindate, @Param("attr1") List<String> attr1, @Param("attr2") String attr2, @Param("attr4") String attr4);

    //成品出库单
    public List<ReportVo> selectCpChd(Page<ReportVo> page, @Param("begindate") String begindate, @Param("attr1")  List<String> attr1, @Param("attr2") String attr2, @Param("attr4") String attr4);

    //生产领料单
    public List<ReportVo> selectLld(Page<ReportVo> page, @Param("commadid") String commadid);

    //退料单
    public List<ReportVo> selectTld(Page<ReportVo> page, @Param("commadid") String commadid);

    //原材料进销存
    public List<ReportVo> selectYclJxc(Page<ReportVo> page, @Param("materid") String materid, @Param("areaCode") String areaCode, @Param("locationCode") String locationCode);

    //导出原材料进销存
    public List<ReportVo> exportXlsYclJxc(@Param("materId") String materId, @Param("areaCode") String areaCode, @Param("locationCode") String locationCode, @Param("ids") List<String> ids);

    //原材料流水报表
    public List<ReportVo> selectLsbb(Page<ReportVo> page, @Param("materid") String materid, @Param("areaCode") String areaCode, @Param("locationCode") String locationCode);

    //成品进销存
    public List<ReportVo> selectCpJxc(Page<ReportVo> page, @Param("materid") String materid, @Param("areaCode") String areaCode, @Param("locationCode") String locationCode);

    /**
     * 根据制令单id查询制令单所有的bomitem上料信息
     *
     * @param query4
     * @return
     */
    @Select("select c.base_code,c.ware_site,c.inware_num,c.unit,c.query2,c.glaze_id, " +
            "(select s.ware_code FROM `jeecg-warehouse`.mes_storage_wholesale s WHERE s.id=c.glaze_id)as query3, " +
            "(select s.query2 FROM `jeecg-warehouse`.mes_storage_wholesale s WHERE s.id=c.glaze_id)as query4, " +
            "(select s.ware_site FROM `jeecg-warehouse`.mes_storage_wholesale s WHERE s.id=c.glaze_id)as query5 " +
            "from `jeecg-warehouse`.mes_storage_wholesale c " +
            "where c.query4=#{query4} " +
            "and c.base_dockettype='扫描上料'")
    public List<MesStorageWholesale> selectQuery4SL(@Param("query4") String query4);

    /**
     * 修改未收货数量和收货状态改为收货完成
     *
     * @param unreceiveNum 未收货数量
     * @param temId        采购订单子表id
     * @return
     */
    @Update("update `jeecg-transaction`.mes_purchase_item set if_finish='收货完成',unreceive_num=#{unreceiveNum} where id=#{temId}")
    int updateNumStatePurchaseItemId(@Param("unreceiveNum") String unreceiveNum, @Param("temId") String temId);

    /**
     * 修改未收货数量
     *
     * @param unreceiveNum 未收货数量
     * @param temId        采购订单子表id
     * @return
     */
    @Update("update `jeecg-transaction`.mes_purchase_item set unreceive_num=#{unreceiveNum} where id=#{temId}")
    int updateNumPurchaseItemId(@Param("unreceiveNum") String unreceiveNum, @Param("temId") String temId);

    /**
     * 通过id查询追踪记录
     *
     * @param id
     * @return
     */
    @Select("select * \n" +
            "from `jeecg-warehouse`.mes_storage_wholesale \n" +
            "where id=#{id} \n" +
            "or query4=#{id} \n" +
            "or query5=#{id} \n" +
            "or glaze_id=#{id};")
    List<MesStorageWholesale> findByIdTraceBack(@Param("id") String id);

    /**
     * 入库单报表导出
     */
    List<YclRkdReport> exportYclRkd(@Param("begindate") String begindate, @Param("attr1") String attr1, @Param("ids") List<String> ids);


    /**
     * 查询已收货数量
     *
     * @param baseCode
     * @return
     */
    @Select("select IFNULL(SUM(inware_num),0)  from `jeecg-warehouse`.mes_storage_wholesale where base_code=#{baseCode} and base_dockettype='扫描收货'")
    int queryPurchaseItem(@Param("baseCode") String baseCode);

    /**
     * 查询出还未收货的已打印物料的列表
     *
     * @param baseCode 采购订单子表id
     * @return
     */
    @Select("select * from mes_storage_wholesale a where a.base_code=#{baseCode} \n" +
            "and a.base_dockettype='收货打印' \n" +
            "and (case when (select inware_num FROM mes_storage_wholesale where base_dockettype='扫描收货' and query4=a.id) is NULL then a.id end) is not null")
    List<MesStorageWholesale> selectPrintUnreceivelist(@Param("baseCode") String baseCode);

    /**
     * 打印了还未收货的数量
     *
     * @param baseCode 采购订单子表id
     * @return
     */
    @Select("select IFNULL(SUM(case when (select inware_num FROM mes_storage_wholesale where base_dockettype='扫描收货' and query4=a.id) is NULL then a.inware_num else 0 end),0) \n" +
            "from mes_storage_wholesale a where a.base_code=#{baseCode} and a.base_dockettype='收货打印'")
    int queryPrintUnreceiveNum(@Param("baseCode") String baseCode);

    @Select("select IFNULL(SUM(t.inware_num),0) from mes_storage_wholesale t where t.base_code=#{baseCode} and t.base_dockettype='扫描发料'")
    int getSumByInWareNum(@Param("baseCode") String baseCode);
}
